package com.itao.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.persistence.TypedQuery;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.itao.framework.paging.Filter;
import com.itao.framework.paging.PagingParam;
import com.itao.framework.paging.PagingResult;
import com.itao.framework.repo.DaoUtils;
import com.itao.model.Account;

/**
 * @Title: AccountDao.java
 * @Description: 
 * @date 2014年1月17日 下午5:06:29
 */

@Repository
public class AccountDao extends AbstractDaoSupport<Account> {
	
	//demo从orm获取sql语句
	public List<Account> ormNativeQuery(long id ) {
    	//方式1.1： 用 spring jdbc, 优点 支持下划线分割的列名与驼峰命名的java属性的自动映射
    	//List<UrmUser> list = getJdbcTemplate().query("select id , name from URM_USER", BeanPropertyRowMapper.newInstance(UrmUser.class));
    	
    	//方式1.2
    	//List<UrmUser> list2 = getJdbcTemplate().query(getNameQuery("testNativeQuery"), BeanPropertyRowMapper.newInstance(UrmUser.class));
    	
    	//方式2.1， 用hibernate的方法
    	Session session = entityManager.unwrap(Session.class);
    	//写在代码里，主要返回的列名字必须和javabean的属性一模一样，下划线要as成驼峰命名
    	//org.hibernate.Query queryInCode = session.createSQLQuery("select id , name from URM_USER").setResultTransformer(Transformers.aliasToBean(UrmUser.class));
    	
    	//写在orm.xml里面，注意orm.xml必须额外加上result-set-mapping="voResultSet",voResultSet如下不用填内容，因为实际上的映射由Transformers完成。
//    	<sql-result-set-mapping name="voResultSet"></sql-result-set-mapping>
    	//如果把sql放到orm.xml里面而没有加 result-set-mapping,将会出现 “Pure native scalar queries are not yet supported” 错误。
    	//意思是说不支持 原生的的直接返回一个标量（String，Object，Integer等）的查询, 需要通过result-set-mapping或者resultClass（resultClass必须是entity）来设置返回值
    	//方式2.2
    	org.hibernate.Query queryInXml = session.getNamedQuery("testNativeParamQuery").setResultTransformer(Transformers.aliasToBean(Account.class));
    	queryInXml.setParameter(0,id);
//    	queryInXml.setParameter(1,companyCode);
    	return queryInXml.list();
    }
	
	public List<Account> findUserByOrgId(Long orgId) {
		String jpql = "FROM UrmUser u where u.status=1 AND u.deleted=0 AND u.urmOrg.id =:orgId or u.id IN (SELECT uom.urmUser.id FROM UrmUserOrgMapping uom WHERE uom.urmOrg.id=:orgId AND uom.effectiveDate<=now() AND uom.expiryDate>=curdate())";
		TypedQuery<Account> query = entityManager.createQuery(jpql, Account.class);
		query.setParameter("orgId", orgId);
		return query.getResultList();
	}
	
	//分页
	@SuppressWarnings("unchecked")
    public PagingResult<Account> findUserPaging(PagingParam pagingParam, Set<String> orgCodes, Boolean isAll){
    	
    	String hql = " from UrmUser u left join fetch u.urmOrg o ";
    	String countHql = "select count(*) from UrmUser u ";
    	StringBuffer wf = new StringBuffer();
    	wf.append(" where 1=1 ");
    	List<Filter> filters = pagingParam.getFilter();
    	List<Map<String,Object>> filterList= DaoUtils.paseFilters(filters,Account.class);
    	int i=0;
    	for(Map<String,Object> map:filterList){
    		
    		wf.append(" and ");
    		if(map.get("property").equals("urmOrg.id"))
    			wf.append("(");
    		if((StringUtils.isEmpty((String)map.get("operator"))))
        		wf.append("u."+map.get("property")+" like :param"+i);
    		else
    			wf.append("u."+map.get("property")+map.get("operator")+" :param"+i);
    		
    		if(map.get("property").equals("urmOrg.id"))//TODO SQL注入漏洞
    			wf.append(" or (u.id IN (SELECT uom.urmUser.id FROM UrmUserOrgMapping uom WHERE uom.urmOrg.id ="+map.get("value")+" AND uom.effectiveDate<=now() AND uom.expiryDate>=curdate())))");
    		i++;
    	}
    	if( !isAll ){
    		wf.append(" and (( ");
    		int n=0;
    		for(String orgCode : orgCodes){
    			if(n!=0)
    				wf.append(" or ");
    			wf.append(" u.urmOrg.orgCode like '"+orgCode+"%'");
    			n++;
    		}
    		wf.append(" ) or u.id IN (SELECT uom.urmUser.id FROM UrmUserOrgMapping uom WHERE ( ");
    		n=0;
    		for(String orgCode:orgCodes){
    			if(n!=0)
    				wf.append(" or ");
    			wf.append(" uom.urmOrg.orgCode like '"+orgCode+"%'");
    			n++;
    		}
    		wf.append(" ) AND uom.effectiveDate<=now() AND uom.expiryDate>=curdate()))");
    		
    	}
    	hql+=wf.toString()+" order by u.id desc ";
    	
    	countHql+=wf.toString();
    	Session session = (Session)entityManager.getDelegate();
    	Query sq = session.createQuery(hql);
    	i=0;
    	for(Map<String,Object> map:filterList){
    		sq.setParameter("param"+i, map.get("value"));
    		i++;
    	}
    	if(pagingParam.getStart()!=null && pagingParam.getLimit()!=null)
    		sq.setFirstResult(pagingParam.getStart())
    			.setMaxResults(pagingParam.getLimit());
    	List<Account> userList = sq.list();
    	
    	Query cq = session.createQuery(countHql);
    	i=0;
    	for(Map<String,Object> map:filterList){
    		cq.setParameter("param"+i, map.get("value"));
    		i++;
    	}
    	long count = (Long)cq.uniqueResult();
    	
    	List<Account> vList = new ArrayList<Account>();
    	return new PagingResult<Account>(vList,(int)count);
    }
}
